package dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author yakimovich
 */
public class CreateTable {

    public static StringBuilder CREATE_TABLESS = new StringBuilder();
    public static StringBuilder DROP_TABLESS = new StringBuilder();
    public static StringBuilder INSERT_ROLES = new StringBuilder();
    public static StringBuilder INSERT_USERS = new StringBuilder();

    static {
        CREATE_TABLESS.append("CREATE TABLE ROLES(");
        CREATE_TABLESS.append("ROLE_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ");
        CREATE_TABLESS.append("NAME VARCHAR(20) NOT NULL UNIQUE, ");
        CREATE_TABLESS.append("PRIMARY KEY (ROLE_ID));");

        CREATE_TABLESS.append("CREATE TABLE USERS(");
        CREATE_TABLESS.append("USER_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, ");
        CREATE_TABLESS.append("LOGIN VARCHAR(20) NOT NULL UNIQUE, ");
        CREATE_TABLESS.append("PASSWORD CHAR(20) NOT NULL, ");
        CREATE_TABLESS.append("EMAIL VARCHAR(60) NOT NULL UNIQUE, ");
        CREATE_TABLESS.append("FIRST_NAME VARCHAR(20) NOT NULL, ");
        CREATE_TABLESS.append("LAST_NAME VARCHAR(40) NOT NULL, ");
        CREATE_TABLESS.append("BIRTHDAY DATE NOT NULL, ");
        CREATE_TABLESS.append("ROLE_ID BIGINT NOT NULL, ");
        CREATE_TABLESS.append("PRIMARY KEY (USER_ID), ");
        CREATE_TABLESS.append("FOREIGN KEY (ROLE_ID) ");
        CREATE_TABLESS.append("REFERENCES ROLES(ROLE_ID));");
    }

    static {
        DROP_TABLESS.append("DROP TABLE IF EXISTS USERS; ");
        DROP_TABLESS.append("DROP TABLE IF EXISTS ROLES; ");
    }

    static {
        INSERT_ROLES.append("INSERT INTO ROLES (NAME) ");
        INSERT_ROLES.append("VALUES ('Admin'); ");
        INSERT_ROLES.append("VALUES ('User');");
    }

    static {
        INSERT_ROLES.append("INSERT INTO ROLES (NAME) ");
        INSERT_ROLES.append("VALUES ('Admin'); ");
        INSERT_ROLES.append("VALUES ('User');");
    }
    
    static {
        INSERT_USERS.append("INSERT INTO USERS ");
        INSERT_USERS.append("(LOGIN, PASSWORD, EMAIL, FIRST_NAME, LAST_NAME, BIRTHDAY, ROLE_ID) ");
        INSERT_USERS.append("VALUES ('Admin', 'pass', 'admin@gmail.com', 'John', 'Brown', '1978-07-30', 1), ");
        INSERT_USERS.append("('Tom', 'pass1', 'tom@gmail.com', 'Tom', 'Jons', '1981-02-05', 1), ");
        INSERT_USERS.append("('Ann', 'pass2', 'ann@gmail.com', 'Ann', 'Smith', '1984-10-07', 2);");
    }

    public static void main(String[] args) throws SQLException {
        Connection conn = new AbstractJdbcDao().createConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            statement.executeUpdate(DROP_TABLESS.toString());
            statement.executeUpdate(CREATE_TABLESS.toString());
            statement.executeUpdate(INSERT_ROLES.toString());
            statement.executeUpdate(INSERT_USERS.toString());
        } catch (SQLException ex) {
            Logger.getLogger(CreateTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
			statement.close();
			JdbcUtils.closeQuietly(conn);
		}

    }
}
